﻿using Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class OrderInfoDAL
    {

        public int AddOrderInfo(OrderInfo orderInfo)
        {
            string sql = "INSERT INTO OrderInfo(SubTime, Remark, NumberOfDiners) " + 
                "VALUES(@stime, @remark, @nod); select last_insert_rowid();";      //发送多条语句，添加并获取主键自增的值

            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@stime", orderInfo.SubTime),
                new SQLiteParameter("@remark", orderInfo.Remark),
                new SQLiteParameter("@nod", orderInfo.NumberOfDiners)
            };


            object obj = SQLiteHelper.ExecuteScalar(sql, parameters);            //发送多条SQL语句

            return Convert.ToInt32(obj);

        }

        public int AddOrderAndDeskInfo(int orderId, int deskId)
        {
            string sql = "insert into Order_Desk(OrderId, DeskId) values(@oid, @did)";

            SQLiteParameter[] sQLiteParameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@oid", orderId),
                new SQLiteParameter("@did", deskId)
            };

            return SQLiteHelper.ExecuteNonQuery(sql, sQLiteParameters);
        }


        public DataTable SelectProductByOrder(int orderId)
        {
            string sql = "select p.ProId, ProName, Proprice, ProCount, ProUnit, procount * Proprice totalprice, CatName, op.SubTime " +
                "from ProductInfo p join categoryInfo c on p.CatId = c.catid join Order_Product op on p.ProId = op.ProId " +
                "where op.OrderId = @ordId and op.DelFlag = 0;";

            SQLiteParameter[] sQLiteParameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@ordId", orderId)
            };

            return SQLiteHelper.ExecuteFill(sql, sQLiteParameters);
        }

        //查询订单商品信息的数量
        public int SelectProCountByOrder(Order_ProductDTO order_ProductDTO)
        {
            string sql = "select ProCount from Order_Product where OrderId = @oid and ProId = @pid and delFlag = 0";

            SQLiteParameter[] sQLiteParameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@oid", order_ProductDTO.OrderId),
                new SQLiteParameter("@pid", order_ProductDTO.ProductId)
            };

            return Convert.ToInt32(SQLiteHelper.ExecuteScalar(sql, sQLiteParameters));
        }

        //添加订单商品信息
        public int AddOrderProducts(Order_ProductDTO order_ProductDTO)
        {
            string sql = "INSERT INTO Order_Product ( OrderId, ProId, ProCount, SubTime ) VALUES ( @OrderId, @ProId, @ProCount, @SubTime );";

            SQLiteParameter[] sQLiteParameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@OrderId", order_ProductDTO.OrderId),
                new SQLiteParameter("@ProId", order_ProductDTO.ProductId),
                new SQLiteParameter("@ProCount", order_ProductDTO.ProCount),
                new SQLiteParameter("@SubTime", order_ProductDTO.SubTime)
            };

            return SQLiteHelper.ExecuteNonQuery(sql, sQLiteParameters);
        }
        //更新订单商品信息
        public int UpdateOrderProducts(Order_ProductDTO order_ProductDTO)
        {
            string sql = "update Order_Product set ProCount = ProCount + 1 where OrderId = @oid and ProId = @pid and delFlag = 0";

            SQLiteParameter[] sQLiteParameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@oid", order_ProductDTO.OrderId),
                new SQLiteParameter("@pid", order_ProductDTO.ProductId)
            };

            return SQLiteHelper.ExecuteNonQuery(sql,sQLiteParameters);
        }


        //查询所有商品的数量
        public int SelectOrderPCount(OrderDTO orderDTO)
        {
            string sql = "select sum(procount) from Order_Product where OrderId = @oid and delFlag = 0";

            SQLiteParameter[] sQLiteParameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@oid", orderDTO.OrderId),
            };

            object v = SQLiteHelper.ExecuteScalar(sql, sQLiteParameters);

            if (DBNull.Value == v)
            {
                return 0;
            }
            return Convert.ToInt32(v);
        }

        //查询商品的金额
        public int SelectOrderPrice(OrderDTO orderDTO)
        {
            string sql = "select sum(proprice * procount) from ProductInfo p join Order_Product op on p.ProId = op.ProId " +
            "where op.orderId = @oid and op.delFlag = 0";

            SQLiteParameter[] sQLiteParameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@oid", orderDTO.OrderId),
            };

            object v = SQLiteHelper.ExecuteScalar(sql, sQLiteParameters);

            if (DBNull.Value == v)
            {
                return 0;
            }
            return Convert.ToInt32(v);
        }

        //根据桌子Id查询最新的订单编号
        public int SelectOrderIdByDeskId(int deskId)
        {
            string sql = "SELECT OrderId FROM Order_Desk where deskId = @did order by Id desc limit 0, 1;";

            SQLiteParameter[] sQLiteParameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@did", deskId)
            };

            return Convert.ToInt32(SQLiteHelper.ExecuteScalar(sql,sQLiteParameters));
        }

        //查询订单中指定商品的数量
        public int SelectProCountByOrderId(int orderid, int productid)
        {
            string sql = "select ProCount from Order_Product where OrderId = @oid and ProId = @pid and delFlag = 0";

            SQLiteParameter[] sQLiteParameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@oid", orderid),
                new SQLiteParameter("@pid", productid)
            };

            return Convert.ToInt32(SQLiteHelper.ExecuteScalar(sql, sQLiteParameters));
        }

        //修改订单中指定商品的数量
        public int UpdateProCountByOrderId(int oid, int pid)
        {
            string sql = "update Order_Product set ProCount = ProCount - 1 where OrderId = @oid and ProId = @pid and delFlag = 0";

            SQLiteParameter[] sQLiteParameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@oid", oid),
                new SQLiteParameter("@pid", pid)
            };

            return SQLiteHelper.ExecuteNonQuery(sql, sQLiteParameters);
        }


        //删除订单中指定商品的数量
        public int DeleteProCountByOrderId(int oid, int pid)
        {
            string sql = "update Order_Product set delFlag = 1  where OrderId = @oid and ProId = @pid and delFlag = 0";

            SQLiteParameter[] sQLiteParameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@oid", oid),
                new SQLiteParameter("@pid", pid)
            };

            return SQLiteHelper.ExecuteNonQuery(sql, sQLiteParameters);
        }


        //修改订单的结单状态
        public int CheckOut(int oid, double money, double discount)
        {
            string sql = "update OrderInfo set OrderState = 1, EndTime = @endTime, OrderMoney = @orderMoney, DisCount = @discount " +
                "where OrderId = @oid and delFlag = 0";

            SQLiteParameter[] sQLiteParameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@oid", oid),
                new SQLiteParameter("@endTime", DateTime.Now),
                new SQLiteParameter("@orderMoney", money),
                new SQLiteParameter("@discount", discount)
            };

            return SQLiteHelper.ExecuteNonQuery(sql, sQLiteParameters);
        }
    }
}
